import pymysql

# 数据库连接配置
db_config = {
    'host': 'localhost',
    'user': 'gxin',
    'password': 'gxin123',
    'database': 'gxin',
    'charset': 'utf8mb4'
}

# 检查数据完整性
def is_data_complete(row):
    # 检查 time, title, hyperlink, project_number, project_name, award_unit, region 是否都不为空
    return all(row[i] is not None and row[i] != "" for i in range(1, 7)) and row[8] is not None and row[8] != ""

# 检查 award_amount 是否为 "未找到中标金额" 或 "0.0000"
def is_award_amount_valid(award_amount):
    return award_amount not in ["未找到中标金额", "0.0000"]

# 清洗数据库中的数据
def clean_database():
    connection = pymysql.connect(**db_config)
    try:
        with connection.cursor() as cursor:
            # 从数据库中查询所有数据
            select_sql = """
            SELECT id, time, title, hyperlink, project_number, project_name, award_unit, award_amount, region
            FROM zfbider_23_info
            """
            cursor.execute(select_sql)
            rows = cursor.fetchall()

            # 遍历所有数据，检查是否需要删除
            for row in rows:
                if not is_data_complete(row) or not is_award_amount_valid(row[7]):
                    # 删除包含空白属性的记录或 award_amount 为 "未找到中标金额" 或 "0.0000" 的记录
                    delete_sql = """
                    DELETE FROM zfbider_23_info
                    WHERE id = %s
                    """
                    cursor.execute(delete_sql, (row[0],))
                    print(f"删除记录: {row}")

        connection.commit()
        print("数据库清洗完成。")

        # 重新打开游标查询清洗后的记录总数
        with connection.cursor() as cursor:
            select_count_sql = """
            SELECT COUNT(*) AS record_count
            FROM zfbider_23_info
            """
            cursor.execute(select_count_sql)
            result = cursor.fetchone()
            record_count = result[0]
            print(f"清洗后，表 zfbider_23_info 中还有 {record_count} 条数据。")
    finally:
        connection.close()

# 调用清洗数据库的函数
clean_database()